﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// 文件名：ClsCheckManageDAL.cs
/// 作  者：黄纲
/// 时  间：2006.11.09
/// 说  明：ClsCheckManageDAL类用于库存管理信息相关处理
/// </summary>
public class ClsCheckManageDAL
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static string strSqlConnectionString = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"];

    /// <summary>
    /// 获取所有库存盘点主表信息
    /// </summary>
    /// <returns>返回库存盘点主表信息数据集</returns>
    public DataSet GetStockCheckMains()
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetStockCheckMains", con);

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 获取所有实盘单确认主表信息×××××××××××××××××××××××××××××××××××
    /// </summary>
    /// <returns>返回库存盘点主表信息数据集</returns>
    public DataSet GetStockCheckAffirmMains()
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetStockCheckAffirmMains", con);

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 根据盘点单号获取所有库存盘点从表信息
    /// <param name="Tm_On">盘点单号</param>
    /// </summary>
    /// <returns>返回库存盘点从表信息数据集</returns>
    public DataSet GetStockCheckChilds(string Tm_No)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetStockCheckD", con);//Pro_GetStockCheckChilds

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@tm_no", SqlDbType.NVarChar, 30);
        dat.SelectCommand.Parameters["@tm_no"].Value = Tm_No;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;       
    }

    public Boolean UpdateStockCheckState(string Tm_No, int State, string Auditor,string sType)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditCheckStockInto", con);//Pro_UpdateStockCheckState  Pro_AuditCheckStock

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@tm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@tm_no"].Value = Tm_No;
        com.Parameters.Add("@code", SqlDbType.NVarChar, 30);
        com.Parameters["@code"].Value = "";
        com.Parameters.Add("@is_audit", SqlDbType.Int, 4);
        com.Parameters["@is_audit"].Value = State;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;
        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 增加实盘单信息
    /// </summary>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    ///strTm_type, strTm_no, dtCheck_date, strStoreId, strOper, strRemark
    public Boolean AddCheckStock(string sTm_type, string sTm_No, DateTime dtCheck_date, string sDp_id, string sTm_Oper, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddCheckStockM", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@tm_type", SqlDbType.NVarChar, 2);
        com.Parameters["@tm_type"].Value = sTm_type;
        com.Parameters.Add("@tm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@tm_no"].Value = sTm_No;
        com.Parameters.Add("@check_date", SqlDbType.DateTime, 8);
        com.Parameters["@check_date"].Value = dtCheck_date;
        com.Parameters.Add("@dp_id", SqlDbType.NVarChar, 30);
        com.Parameters["@dp_id"].Value = sDp_id;
        com.Parameters.Add("@Tm_Oper", SqlDbType.NVarChar, 30);
        com.Parameters["@Tm_Oper"].Value = sTm_Oper;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 增加实盘单明细信息
    /// </summary>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    ///strTm_type, strTm_no, dtCheck_date, strStoreId, strOper, strRemark
    public Boolean AddChildCheckStock(string sTd_type, string sTm_No,string sIm_no, string sLot_no, string sStoreId,
                               decimal dQty, decimal dQty1, decimal dDiversityQty, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddCheckStockD", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@td_type", SqlDbType.NVarChar, 2);
        com.Parameters["@td_type"].Value = sTd_type;
        com.Parameters.Add("@tm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@tm_no"].Value = sTm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = sIm_no;
        com.Parameters.Add("@lot_no", SqlDbType.NVarChar, 30);
        com.Parameters["@lot_no"].Value = sLot_no;
        com.Parameters.Add("@whi_id", SqlDbType.NVarChar, 30);
        com.Parameters["@whi_id"].Value = sStoreId;
        com.Parameters.Add("@qty", SqlDbType.Decimal,28);
        com.Parameters["@qty"].Value = dQty;
        com.Parameters.Add("@qty1", SqlDbType.Decimal,28);
        com.Parameters["@qty1"].Value = dQty1;
        com.Parameters.Add("@diversityQty", SqlDbType.Decimal, 28);
        com.Parameters["@diversityQty"].Value = dDiversityQty;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改实盘单信息
    /// </summary>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    ///strTm_type, strTm_no, dtCheck_date, strStoreId, strOper, strRemark
    public Boolean UpdateCheckStock(string sTm_type, string sTm_No, DateTime dtCheck_date, string sDp_id, string sTm_Oper, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateCheckStock", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@tm_type", SqlDbType.NVarChar, 2);
        com.Parameters["@tm_type"].Value = sTm_type;
        com.Parameters.Add("@tm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@tm_no"].Value = sTm_No;
        com.Parameters.Add("@check_date", SqlDbType.DateTime, 8);
        com.Parameters["@check_date"].Value = dtCheck_date;
        com.Parameters.Add("@dp_id", SqlDbType.NVarChar, 30);
        com.Parameters["@dp_id"].Value = sDp_id;
        com.Parameters.Add("@Tm_Oper", SqlDbType.NVarChar, 30);
        com.Parameters["@Tm_Oper"].Value = sTm_Oper;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改实盘单明细信息
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    //string sTd_type, string sTm_No, string sIm_no, string sLot_no, string sStoreId,decimal dQty, decimal dQty1, decimal dDiversityQty, string sRemark
    public Boolean UpDateChildCheckStock(string sTd_type, string sTm_No, string sIm_no, string sLot_no, string sStoreId, decimal dQty, decimal dQty1, decimal dDiversityQty, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateCheckStockD", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@td_type", SqlDbType.NVarChar, 2);
        com.Parameters["@td_type"].Value = sTd_type;
        com.Parameters.Add("@tm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@tm_no"].Value = sTm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = sIm_no;
        com.Parameters.Add("@lot_no", SqlDbType.NVarChar, 30);
        com.Parameters["@lot_no"].Value = sLot_no;
        com.Parameters.Add("@whi_id", SqlDbType.NVarChar, 30);
        com.Parameters["@whi_id"].Value = sStoreId;
        com.Parameters.Add("@qty", SqlDbType.Decimal, 28);
        com.Parameters["@qty"].Value = dQty;
        com.Parameters.Add("@qty1", SqlDbType.Decimal, 28);
        com.Parameters["@qty1"].Value = dQty1;
        com.Parameters.Add("@diversityQty", SqlDbType.Decimal, 28);
        com.Parameters["@diversityQty"].Value = dDiversityQty;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废实盘单信息
    /// </summary>
    /// <param name="sTm_type"></param>
    /// <param name="sTm_no"></param>
    /// <param name="iIs_Visible">作废状态1为临时作废，－1为彻底作废</param>
    /// <returns></returns>
    public Boolean DeleteCheckStock(string sTm_type, string sTm_no, int iIs_Visible)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteStockCheck", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@tm_type", SqlDbType.NVarChar, 2);
        com.Parameters["@tm_type"].Value = sTm_type;
        com.Parameters.Add("@tm_no", SqlDbType.NVarChar, 20);
        com.Parameters["@tm_no"].Value = sTm_no;
        com.Parameters.Add("@Is_Visible", SqlDbType.Int, 1);
        com.Parameters["@Is_Visible"].Value = iIs_Visible;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废实盘单明细信息
    /// </summary>
    /// <param name="Is_Visible">作废状态－1为临时作废，－2为彻底作废</param>
    /// <returns>返回作废是否成功</returns>
    public Boolean DeleteChildSellBack(string sTd_type, string sTm_No, string sIm_no, string sLot_no, int iIs_Visible)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteChildCheckStock", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@td_type", SqlDbType.NVarChar, 2);
        com.Parameters["@td_type"].Value = sTd_type;
        com.Parameters.Add("@tm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@tm_no"].Value = sTm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = sIm_no;
        com.Parameters.Add("@lot_no", SqlDbType.NVarChar, 30);
        com.Parameters["@lot_no"].Value = sLot_no;
        com.Parameters.Add("@Is_Visible", SqlDbType.Int, 1);
        com.Parameters["@Is_Visible"].Value = iIs_Visible;


        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    ///  实盘单确认 
    /// <returns>CREATE BY KIDY ON 20061214</returns>
    /// </summary>
    public String CheckWhetherAudtit(string sTm_No, string sTm_Type, int iState, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditCheckStockChangeStore", con);
        com.CommandType = CommandType.StoredProcedure;
        SqlParameter ParOutPut = new SqlParameter();

        com.Parameters.Add("@tm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@tm_no"].Value = sTm_No;
        com.Parameters.Add("@dm_state", SqlDbType.Int, 4);
        com.Parameters["@dm_state"].Value = iState;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;
        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sTm_Type;
        ParOutPut = com.Parameters.Add("@relot_no", SqlDbType.NVarChar, 30);
        ParOutPut.Direction = ParameterDirection.Output;
        ParOutPut = com.Parameters.Add("@returnState", SqlDbType.NVarChar, 2);
        ParOutPut.Direction = ParameterDirection.Output;
        try
        {
            con.Open();
            com.ExecuteNonQuery();
            string strState = com.Parameters["@returnState"].Value.ToString();
            string strReLot_no = com.Parameters["@relot_no"].Value.ToString();
            if (strState == "N")
            {
                return "N" + strReLot_no;
            }
            else if (strState == "Q")
            {
                return "Q" + strReLot_no;
            }
            else
            {
                return "0";
            }

            return strReLot_no;
        }
        catch
        {
            return "";
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

}
